package cn.pengpeng.hiveanli1;

public class Sql {
	public static void main(String[] args) {

	}
/**
 *
--创建Hive表weibo(json STRING)，导入所有数据，并验证查询前3条数据。
CREATE EXTERNAL TABLE `weibo`(`json` string)
LOCATION '/weibo';

--装载数据
load data local inpath '/usr/local/all.json' into table weibo;

--创建临时表
CREATE TABLE weibo_tmp 
    AS 
SELECT SUBSTRING(json,2,length(json)-2) AS json_tmp 
  FROM weibo;

--创建19个字段的表
CREATE TABLE weibo_t(
 beCommentWeiboId STRING,
 beForwardWeiboId STRING,
 catchTime STRING,
 commentCount INT,
 content STRING,
 createTime STRING,
 info1 STRING, 
 info2 STRING, 
 info3 STRING,
 mlevel STRING, 
 musicurl STRING, 
 pic_list STRING, 
 praiseCount INT,
 reportCount INT, 
 source STRING, 
 userId STRING, 
 videourl STRING,
 weiboId STRING, 
 weiboUrl STRING 
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t';

--装载数据
--解析json数据并插入weibo表：
INSERT INTO TABLE weibo_t 
SELECT get_json_object(json_tmp,'$.beCommentWeiboId') beCommentWeiboId
       ,get_json_object(json_tmp,'$.beForwardWeiboId') beForwardWeiboId
       ,get_json_object(json_tmp,'$.catchTime')  catchTime
       ,get_json_object(json_tmp,'$.commentCount') commentCount
       ,get_json_object(json_tmp,'$.content')  content
       ,get_json_object(json_tmp,'$.createTime') createTime
       ,get_json_object(json_tmp,'$.info1') info1
       ,get_json_object(json_tmp,'$.info2') info2
       ,get_json_object(json_tmp,'$.info3') info3
       ,get_json_object(json_tmp,'$.mlevel') mlevel
       ,get_json_object(json_tmp,'$.musicurl') musicurl
       ,get_json_object(json_tmp,'$.pic_list') pic_list
       ,get_json_object(json_tmp,'$.praiseCount') praiseCount
       ,get_json_object(json_tmp,'$.reportCount') reportCount
       ,get_json_object(json_tmp,'$.source') source
       ,get_json_object(json_tmp,'$.userId') userId
       ,get_json_object(json_tmp,'$.videourl') videourl
       ,get_json_object(json_tmp,'$.weiboId') weiboId
       ,get_json_object(json_tmp,'$.weiboUrl') weiboUrl 
 FROM weibo_tmp ;
 
 
--3、统计微博总量 和 独立用户数
--微博总量
SELECT COUNT(*) FROM weibo_t;

结果：1451868

--独立用户数：
SELECT COUNT(DISTINCT userid) FROM weibo_t;

结果：78540

--4、统计用户所有微博被转发的次数之和，输出top5用户，并给出次数
SELECT userid, 
       SUM(reportcount) AS totalno, 
       COUNT(*) AS weibono 
  FROM weibo_t 
 GROUP BY userid 
 ORDER BY totalno DESC 
 LIMIT 5;
 
结果：
1793285524      76397964        1409
1629810574      73656898        1243
2803301701      68176008        3443
1266286555      55111054        278
1191258123      54808042        411

--5、统计带图片的微博数
SELECT COUNT(*) picweibo 
  FROM weibo_t 
 WHERE pic_list 
  LIKE '%http%';

结果：750512

--6、统计使用iphone发微博的独立用户数
SELECT COUNT(DISTINCT userid) 
  FROM weibo_t 
 WHERE LOWER(source) 
  LIKE '%iphone%';

结果：936

--7、将用户所有微博的点赞人数和转发人数相加求和，并将相加之和降序排列，取前10条记录，输出userid和总次数

SELECT userid, 
       SUM(praisecount+reportcount) AS totalno 
  FROM weibo_t 
 GROUP BY userid 
 ORDER BY totalno DESC 
 LIMIT 10;
 
结果：
1793285524      114883638
1629810574      97612070
1266286555      83789422
2803301701      74208822
1195242865      69292231
1191258123      61985742
1197161814      59093308
2656274875      52380775
2202387347      51623117
1195230310      48321083

--8、视图， ipad客户端
CREATE VIEW weibo_source_view AS 
SELECT userid, source 
  FROM weibo_t 
 WHERE commentcount < 1000;
--查询语句
SELECT COUNT(DISTINCT userID) 
  FROM weibo_source_view 
 WHERE source='iPad客户端';
 
结果：537

--9、出现’iphone’次数最多（创建UDF实现）
package cn.pengpeng.hive.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
public class IphoneNumberUDF extends UDF {
	// 求info在infos当中出现的次数
	public int evaluate(String infos, String info) {
		try {
			int count = 0;
			while (infos.indexOf(info) != -1) {
				count++;
				infos = infos.substring(infos.indexOf(info) + info.length());
			}
			return count;
		} catch (Exception e) {
			return 0;
		}
	}
}

--打jar，然后加入到hive:
ADD jar /home/lan/jar/myiphoneudf.jar;
--创建临时函数：
CREATE TEMPORARY FUNCTION getIphoneNo AS 'com.udf_weibo.org.IphoneNumberUDF';
--SQL语句：
SELECT userid, 
       SUM(getIphoneNo(LCASE(content), 'iphone')) AS totalno 
  FROM weibo_t 
 GROUP BY userid 
 ORDER BY totalno DESC 
 LIMIT 1;
 
--销毁临时函数
drop temporary function getIphoneNo;

结果：
1781387491      512

--10、每天发微博最多的家伙的ID和发微博的条数
SELECT num2.weibo_date
      ,num2.userid
      ,num2.num
  FROM(SELECT num1.weibo_date
             ,MAX(num) AS num_max
         FROM(SELECT from_unixtime(CAST(createtime AS INT), 'yyyy-MM-dd') AS weibo_date
                    ,userid
                    ,COUNT(*) AS num
                FROM weibo_t
               GROUP BY from_unixtime(CAST(createtime AS INT), 'yyyy-MM-dd'),userid
              )AS num1
         GROUP BY num1.weibo_date
       )AS max_num
  JOIN(SELECT from_unixtime(CAST(createtime AS INT), 'yyyy-MM-dd') AS weibo_date
             ,userid
             ,COUNT(*) AS num
         FROM weibo_t
        GROUP BY from_unixtime(CAST(createtime AS INT), 'yyyy-MM-dd'),userid
       )AS num2
    ON max_num.weibo_date = num2.weibo_date
   AND max_num.num_max = num2.num
   ORDER BY num2.weibo_date
;

--结果
2012-08-15	1646724250	20
2012-08-16	1296947890	25
2012-08-17	1322492412	21
2012-08-18	1646724250	24
2012-08-19	1041212215	22
2012-08-20	1322492412	26
2012-08-21	1052937860	33
2012-08-22	1322492412	53
2012-08-23	1816011541	31
2012-08-24	1768249682	25
2012-08-25	1980923321	28
2012-08-26	1646724250	21
2012-08-27	1340406064	34
2012-08-28	1646724250	25
2012-08-29	1646724250	29
2012-08-30	1646724250	36
2012-08-31	1644700760	29
2012-08-31	1646724250	29
2012-09-01	1646724250	33
2012-09-02	1582160525	45
2012-09-03	1646724250	20
2012-09-04	1052937860	29
2012-09-05	1180721740	39
2012-09-06	1646724250	32


--使用开窗函数--
SELECT b.weibo_date
      ,b.userid
      ,b.num
  FROM(
  SELECT a.*
        ,ROW_NUMBER() OVER(PARTITION BY a.weibo_date ORDER BY a.num DESC) AS leve
    FROM(
    SELECT from_unixtime(CAST(createtime AS INT), 'yyyy-MM-dd') AS weibo_date
          ,userid
          ,COUNT(1) AS num
      FROM weibo_t
     GROUP BY from_unixtime(CAST(createtime AS INT), 'yyyy-MM-dd')
             ,userid
         ) a
       )b 
 WHERE b.leve=1

2013-12-01      1618051664      81
2013-12-02      1618051664      85
2013-12-03      1618051664      76
2013-12-04      1649159940      95
2013-12-05      1618051664      94
2013-12-06      1618051664      76
2013-12-07      1618051664      71
2013-12-08      1618051664      102
2013-12-09      3921730119      99
2013-12-10      1663937380      81
2013-12-11      1740577714      89
2013-12-12      1713926427      130
2013-12-13      1713926427      163
2013-12-14      1638781994      168
2013-12-15      1406387602      136
 */

}
